Function based indexes

WHERE clauses that modify the value of an indexed column will not use an index. Similarly, WHERE clauses that modify the value of a cluster key column will not perform a hash cluster scan.

For example:

• WHERE monthly_sal*12 > 100000
• WHERE SUBSTR(key,1,3) = 'FOO'
• WHERE initial || ' ' || surname = 'J BLOGGS'

In order to use an index or hash cluster scan, the indexed column / cluster key must appear unaltered in the WHERE clause. Often it is possible to overcome this problem by restructuring your WHERE clause

• WHERE monthly_sal > 100000 / 12
• WHERE key LIKE 'FOO%'
• WHERE initial = 'J' and surname = 'BLOGGS'

If your query cannot be restructured and the query is critical to your system, you may be able to use a function based index. A function based index allows you to create an index over a function of column instead of the column itself.

eg.

CREATE INDEX my_table_i1
ON my_table(substr(my_col,2,5))